const pool = require('../config/db')

/**
 * 菜品模型
 */
const Dish = {
  /**
   * 分页条件查询菜品列表
   */
  findAll: async ({ name, categoryIds, tasteIds, difficulty, cookTime, page, pageSize }) => {
    // 转换参数类型
    const pageNum = page || 1
    const pageSizeNum = pageSize || 10
    const offset = (pageNum - 1) * pageSizeNum

    // 构建基础查询
    let sql = `
      SELECT DISTINCT d.dish_id, d.name, d.difficulty, d.cook_time, d.created_at
      FROM dishes d
    `

    // 添加连接条件
    const joins = []
    const whereConditions = []
    const params = []

    // 分类过滤（修复数组类型处理）
    if (categoryIds?.length > 0) {
      joins.push('JOIN dish_categories dc ON d.dish_id = dc.dish_id')
      whereConditions.push('dc.category_id IN (?)')
      params.push(Array.isArray(categoryIds) ? categoryIds : [categoryIds])
    }

    // 口味过滤（修复数组类型处理）
    if (tasteIds?.length > 0) {
      joins.push('JOIN dish_tastes dt ON d.dish_id = dt.dish_id')
      whereConditions.push('dt.taste_id IN (?)')
      params.push(Array.isArray(tasteIds) ? tasteIds : [tasteIds])
    }

    // 名称模糊搜索（添加trim处理）
    if (name?.trim()) {
      whereConditions.push('d.name LIKE ?')
      params.push(`%${name.trim()}%`)
    }

    // 难度过滤
    if (difficulty?.length > 0) {
      whereConditions.push('d.difficulty IN (?)')
      params.push(Array.isArray(difficulty) ? difficulty : [difficulty])
    }

    // 时长过滤
    if (cookTime?.length > 0) {
      whereConditions.push('d.cook_time IN (?)')
      params.push(Array.isArray(cookTime) ? cookTime : [cookTime])
    }

    // 组合SQL
    if (joins.length > 0) {
      sql += ' ' + joins.join(' ')
    }

    if (whereConditions.length > 0) {
      sql += ' WHERE ' + whereConditions.join(' AND ')
    }

    // 修复COUNT查询（使用相同JOIN条件）
    const countJoins = joins.join(' ')
    const countWhere = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : ''
    const countSql = `SELECT COUNT(DISTINCT d.dish_id) as total 
      FROM dishes d 
      ${countJoins} 
      ${countWhere}`

    // 执行COUNT查询（使用独立参数）
    const [countResult] = await pool.query(countSql, [...params])
    const total = countResult[0].total

    // 分页和排序（添加分页参数）
    sql += ' ORDER BY d.created_at DESC LIMIT ? OFFSET ?'
    const queryParams = [...params, pageSizeNum, offset]

    // 执行主查询
    const [rows] = await pool.query(sql, queryParams)

    // 获取每个菜品的分类、口味和主图
    const dishIds = rows.map((row) => row.dish_id)

    // 如果没有菜品，直接返回空结果
    if (dishIds.length === 0) {
      return {
        data: {
          data: [],
          pageSize: pageSizeNum,
          pageNumber: pageNum,
          total: 0,
        },
      }
    }

    // 查询分类
    const [categories] = await pool.query(
      `SELECT dc.dish_id, c.category_name 
       FROM dish_categories dc 
       JOIN categories c ON dc.category_id = c.category_id 
       WHERE dc.dish_id IN (?)`,
      [dishIds]
    )

    // 查询口味
    const [tastes] = await pool.query(
      `SELECT dt.dish_id, t.taste_name 
       FROM dish_tastes dt 
       JOIN tastes t ON dt.taste_id = t.taste_id 
       WHERE dt.dish_id IN (?)`,
      [dishIds]
    )

    // 查询主图
    const [images] = await pool.query(
      `SELECT dish_id, image_path 
       FROM images 
       WHERE dish_id IN (?) AND step_id IS NULL 
       ORDER BY uploaded_at ASC`,
      [dishIds]
    )

    // 组装结果
    const dishMap = {}
    rows.forEach((dish) => {
      dishMap[dish.dish_id] = {
        dishId: dish.dish_id,
        name: dish.name,
        difficulty: dish.difficulty,
        cookTime: dish.cook_time,
        categories: [],
        tastes: [],
        mainImage: null,
      }
    })

    // 添加分类
    categories.forEach((cat) => {
      if (dishMap[cat.dish_id]) {
        dishMap[cat.dish_id].categories.push(cat.category_name)
      }
    })

    // 添加口味
    tastes.forEach((taste) => {
      if (dishMap[taste.dish_id]) {
        dishMap[taste.dish_id].tastes.push(taste.taste_name)
      }
    })

    // 添加主图
    images.forEach((img) => {
      if (dishMap[img.dish_id] && !dishMap[img.dish_id].mainImage) {
        dishMap[img.dish_id].mainImage = img.image_path
      }
    })

    // 转换为数组
    const result = Object.values(dishMap)

    return {
      data: result,
      pageSize: pageSizeNum,
      pageNumber: pageNum,
      total,
    }
  },

  /**
   * 根据ID查询菜品详情
   */
  findById: async (dishId) => {
    // 查询菜品基本信息
    const [dishes] = await pool.query(
      'SELECT dish_id, name, difficulty, cook_time FROM dishes WHERE dish_id = ?',
      [dishId]
    )

    if (dishes.length === 0) {
      return null
    }

    const dish = dishes[0]

    // 查询分类
    const [categories] = await pool.query(
      `SELECT c.category_id, c.category_name 
       FROM dish_categories dc 
       JOIN categories c ON dc.category_id = c.category_id 
       WHERE dc.dish_id = ?`,
      [dishId]
    )

    // 查询口味
    const [tastes] = await pool.query(
      `SELECT t.taste_id, t.taste_name 
       FROM dish_tastes dt 
       JOIN tastes t ON dt.taste_id = t.taste_id 
       WHERE dt.dish_id = ?`,
      [dishId]
    )

    // 查询步骤
    const [steps] = await pool.query(
      `SELECT step_id, step_name, sequence, description, duration_value, duration_unit 
       FROM steps 
       WHERE dish_id = ? 
       ORDER BY sequence`,
      [dishId]
    )

    // 查询图片
    const [images] = await pool.query(
      `SELECT image_id, dish_id, step_id, image_path 
       FROM images 
       WHERE dish_id = ? 
       ORDER BY uploaded_at`,
      [dishId]
    )

    // 查询食材
    const [ingredients] = await pool.query(
      `SELECT ingredient_name, quantity FROM dish_ingredients WHERE dish_id = ?`,
      [dishId]
    )

    // 组装结果
    const result = {
      dishId: dish.dish_id,
      name: dish.name,
      difficulty: dish.difficulty,
      cookTime: dish.cook_time,
      categories: categories.map((c) => c.category_name),
      categoryIds: categories.map((c) => c.category_id),
      tastes: tastes.map((t) => t.taste_name),
      tasteIds: tastes.map((t) => t.taste_id),
      steps: (() => {
        // 按 step_name 分组
        const group = {}
        steps.forEach((s) => {
          const stepImages = images
            .filter((img) => img.step_id === s.step_id)
            .map((img) => img.image_path)

          if (!group[s.step_name]) {
            group[s.step_name] = []
          }
          group[s.step_name].push({
            sequence: s.sequence,
            description: s.description,
            duration_value: s.duration_value,
            duration_unit: s.duration_unit,
            images: stepImages,
          })
        })
        // 转换为数组
        return Object.entries(group).map(([step_name, items]) => ({
          stepName: step_name,
          steps: items,
        }))
      })(),
      mainImage: images.find((img) => img.step_id === null)?.image_path || null,
      ingredients: ingredients.map(i => ({ ingredientName: i.ingredient_name, quantity: i.quantity }))
    }

    return result
  },

  /**
   * 创建菜品
   */
  create: async ({ name, difficulty, cookTime, categories, tastes, steps, mainImage, ingredients }) => {
    const connection = await pool.getConnection()
    try {
      await connection.beginTransaction()

      // 插入菜品基本信息
      const [dishResult] = await connection.query(
        'INSERT INTO dishes (name, difficulty, cook_time) VALUES (?, ?, ?)',
        [name, difficulty, cookTime]
      )

      const dishId = dishResult.insertId

      // 插入分类关联
      if (categories && categories.length > 0) {
        const categoryValues = categories.map((categoryId) => [dishId, categoryId])
        await connection.query('INSERT INTO dish_categories (dish_id, category_id) VALUES ?', [
          categoryValues,
        ])
      }

      // 插入口味关联
      if (tastes && tastes.length > 0) {
        const tasteValues = tastes.map((tasteId) => [dishId, tasteId])
        await connection.query('INSERT INTO dish_tastes (dish_id, taste_id) VALUES ?', [
          tasteValues,
        ])
      }

      // 插入食材
      if (ingredients && ingredients.length > 0) {
        const ingredientValues = ingredients.map(i => [dishId, i.ingredientName, i.quantity])
        await connection.query('INSERT INTO dish_ingredients (dish_id, ingredient_name, quantity) VALUES ?', [ingredientValues])
      }

      // 插入步骤（支持分组结构）
      if (steps && steps.length > 0) {
        let stepSeq = 1
        for (const group of steps) {
          const stepName = group.stepName
          for (const step of group.steps) {
            const durationValue = step.duration_value === '' ? null : step.duration_value
            const durationUnit = step.duration_unit || '分钟'
            const [stepResult] = await connection.query(
              'INSERT INTO steps (dish_id, step_name, sequence, description, duration_value, duration_unit) VALUES (?, ?, ?, ?, ?, ?)',
              [dishId, stepName, step.sequence ?? stepSeq, step.description, durationValue, durationUnit]
            )
            const stepId = stepResult.insertId

            // 插入步骤图片
            if (step.images && step.images.length > 0) {
              const imageValues = step.images.map((imagePath) => [dishId, stepId, imagePath])
              await connection.query('INSERT INTO images (dish_id, step_id, image_path) VALUES ?', [
                imageValues,
              ])
            }
            stepSeq++
          }
        }
      }

      // 插入主图
      if (mainImage) {
        await connection.query(
          'INSERT INTO images (dish_id, step_id, image_path) VALUES (?, NULL, ?)',
          [dishId, mainImage]
        )
      }

      await connection.commit()
      return dishId
    } catch (error) {
      await connection.rollback()
      throw error
    } finally {
      connection.release()
    }
  },

  /**
   * 更新菜品
   */
  update: async (dishId, { name, difficulty, cookTime, categories, tastes, steps, mainImage, ingredients }) => {
    const connection = await pool.getConnection()
    try {
      await connection.beginTransaction()

      // 更新菜品基本信息
      await connection.query(
        'UPDATE dishes SET name = ?, difficulty = ?, cook_time = ? WHERE dish_id = ?',
        [name, difficulty, cookTime, dishId]
      )

      // 删除旧的分类关联
      await connection.query('DELETE FROM dish_categories WHERE dish_id = ?', [dishId])

      // 插入新的分类关联
      if (categories && categories.length > 0) {
        const categoryValues = categories.map((categoryId) => [dishId, categoryId])
        await connection.query('INSERT INTO dish_categories (dish_id, category_id) VALUES ?', [
          categoryValues,
        ])
      }

      // 删除旧的口味关联
      await connection.query('DELETE FROM dish_tastes WHERE dish_id = ?', [dishId])

      // 插入新的口味关联
      if (tastes && tastes.length > 0) {
        const tasteValues = tastes.map((tasteId) => [dishId, tasteId])
        await connection.query('INSERT INTO dish_tastes (dish_id, taste_id) VALUES ?', [
          tasteValues,
        ])
      }

      // 删除旧的食材
      await connection.query('DELETE FROM dish_ingredients WHERE dish_id = ?', [dishId])
      // 插入新的食材
      if (ingredients && ingredients.length > 0) {
        const ingredientValues = ingredients.map(i => [dishId, i.ingredientName, i.quantity])
        await connection.query('INSERT INTO dish_ingredients (dish_id, ingredient_name, quantity) VALUES ?', [ingredientValues])
      }

      // 获取旧的步骤ID
      const [oldSteps] = await connection.query('SELECT step_id FROM steps WHERE dish_id = ?', [
        dishId,
      ])
      const oldStepIds = oldSteps.map((step) => step.step_id)

      // 删除旧的步骤和图片
      if (oldStepIds.length > 0) {
        await connection.query('DELETE FROM images WHERE step_id IN (?)', [oldStepIds])
        await connection.query('DELETE FROM steps WHERE dish_id = ?', [dishId])
      }

      // 插入新的步骤（支持分组结构）
      if (steps && steps.length > 0) {
        let stepSeq = 1
        for (const group of steps) {
          const stepName = group.stepName
          for (const step of group.steps) {
            const durationValue = step.duration_value === '' ? null : step.duration_value
            const durationUnit = step.duration_unit || '分钟'
            const [stepResult] = await connection.query(
              'INSERT INTO steps (dish_id, step_name, sequence, description, duration_value, duration_unit) VALUES (?, ?, ?, ?, ?, ?)',
              [dishId, stepName, step.sequence ?? stepSeq, step.description, durationValue, durationUnit]
            )
            const stepId = stepResult.insertId

            // 插入步骤图片
            if (step.images && step.images.length > 0) {
              const imageValues = step.images.map((imagePath) => [dishId, stepId, imagePath])
              await connection.query('INSERT INTO images (dish_id, step_id, image_path) VALUES ?', [
                imageValues,
              ])
            }
            stepSeq++
          }
        }
      }

      // 更新主图
      await connection.query('DELETE FROM images WHERE dish_id = ? AND step_id IS NULL', [dishId])

      if (mainImage) {
        await connection.query(
          'INSERT INTO images (dish_id, step_id, image_path) VALUES (?, NULL, ?)',
          [dishId, mainImage]
        )
      }

      await connection.commit()
    } catch (error) {
      await connection.rollback()
      throw error
    } finally {
      connection.release()
    }
  },

  /**
   * 检查菜品是否存在
   */
  exists: async (dishId) => {
    const [rows] = await pool.query('SELECT 1 FROM dishes WHERE dish_id = ? LIMIT 1', [dishId])
    return rows.length > 0
  },

  /**
   * 删除菜品
   */
  delete: async (dishId) => {
    const connection = await pool.getConnection()
    try {
      await connection.beginTransaction()

      // 获取步骤ID
      const [steps] = await connection.query('SELECT step_id FROM steps WHERE dish_id = ?', [
        dishId,
      ])

      const stepIds = steps.map((step) => step.step_id)

      // 删除图片
      await connection.query('DELETE FROM images WHERE dish_id = ?', [dishId])

      // 删除步骤
      if (stepIds.length > 0) {
        await connection.query('DELETE FROM steps WHERE dish_id = ?', [dishId])
      }

      // 删除分类关联
      await connection.query('DELETE FROM dish_categories WHERE dish_id = ?', [dishId])

      // 删除口味关联
      await connection.query('DELETE FROM dish_tastes WHERE dish_id = ?', [dishId])

      // 删除菜品
      await connection.query('DELETE FROM dishes WHERE dish_id = ?', [dishId])

      await connection.commit()
    } catch (error) {
      await connection.rollback()
      throw error
    } finally {
      connection.release()
    }
  },
}

module.exports = Dish
